if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_day_user_jial')
          and type in ('P','PC'))
DROP PROCEDURE bd_proc_day_user_jial
GO
CREATE PROCEDURE bd_proc_day_user_jial
	@yesterday date
as
BEGIN

-- 关闭打印受影响行数
SET NOCOUNT ON;


-- 定义返回受影响行数结果
	declare @result int = 0;
	--行转列属性
	declare @sqlWPName VARCHAR(max);
	--查询列去null为0
	declare @sqlWPName2 VARCHAR(max);
	--执行SQL
	declare @sqlPivot VARCHAR(max);

	--  异常扑捉机制
		BEGIN TRY

			--创建临时表
			CREATE TABLE #temp(
				user_serial INT,
				date date,
				bh VARCHAR(max),
				duration INT
			)

		-- 开启事务
			BEGIN TRAN

				--删除当前日期的记录
				delete from bd_day_user_jial where date = @yesterday;

				--行转列属性
				select @sqlWPName = STUFF((SELECT  ','+'jiaL_'+bh FROM( SELECT  DISTINCT bh FROM kt_qingj)T FOR XML PATH('') ),1,1,'');

				--查询列去null为0
				select @sqlWPName2 = STUFF((SELECT  ','+'isnull(jiaL_'+bh+',0)' FROM( SELECT  DISTINCT bh FROM kt_qingj)T FOR XML PATH('') ),1,1,'');

-- 				PRINT @sqlWPName;
-- 				PRINT @sqlWPName2;

				--添加用户ID,日期,编号,时长到临时表
				INSERT INTO #temp (user_serial,date,bh,duration)
				select user_serial,@yesterday,'jiaL_'+bh,jsjg from lr_kqingj where  CONVERT(VARCHAR(10),czrq,120) = @yesterday and whether_validity = 1;

				--处理假类数据
				SELECT @sqlPivot = '
					INSERT INTO bd_day_user_jial
					SELECT
						user_serial,
						date,
						'+@sqlWPName2+'
					FROM (
						SELECT *
						FROM dbo.#temp
					) t
					PIVOT (
						SUM(duration)
						FOR bh IN (
								'+@sqlWPName+'
						)
					) p';

-- 				PRINT @sqlPivot;

				--执行SQL
				EXEC (@sqlPivot);

-- 				INSERT INTO bd_day_user_jial
-- 				SELECT
-- 					user_serial,
-- 					date,
-- 					isnull(jiaL_00101,0),
-- 					isnull(jiaL_00102,0),
-- 					isnull(jiaL_00103,0),
-- 					isnull(jiaL_00104,0),
-- 					isnull(jiaL_00105,0),
-- 					isnull(jiaL_00106,0),
-- 					isnull(jiaL_00107,0),
-- 					isnull(jiaL_00108,0),
-- 					isnull(jiaL_00109,0),
-- 					isnull(jiaL_00110,0),
-- 					isnull(jiaL_00111,0),
-- 					isnull(jiaL_00112,0),
-- 					isnull(jiaL_00113,0),
-- 					isnull(jiaL_00114,0),
-- 					isnull(jiaL_00115,0),
-- 					isnull(jiaL_00116,0),
-- 					isnull(jiaL_00117,0),
-- 					isnull(jiaL_00118,0)
-- 				FROM (
-- 					SELECT *
-- 					FROM dbo.#temp
-- 				) t
-- 				PIVOT (
-- 					SUM(duration)
-- 					FOR bh IN (
-- 							jiaL_00101,
-- 							jiaL_00102,
-- 							jiaL_00103,
-- 							jiaL_00104,
-- 							jiaL_00105,
-- 							jiaL_00106,
-- 							jiaL_00107,
-- 							jiaL_00108,
-- 							jiaL_00109,
-- 							jiaL_00110,
-- 							jiaL_00111,
-- 							jiaL_00112,
-- 							jiaL_00113,
-- 							jiaL_00114,
-- 							jiaL_00115,
-- 							jiaL_00116,
-- 							jiaL_00117,
-- 							jiaL_00118
-- 					)
-- 				) p;

				-- 出差  00101
				-- 公出  00102
				-- 培训  00103
				-- 公假  00104
				-- 调休  00105
				-- 年假  00106
				-- 病假  00107
				-- 事假  00108
				-- 婚假  00109
				-- 丧假  00110
				-- 探亲  00111
				-- 工伤  00112
				-- 放班  00113
				-- 产前假  00114
				-- 产假  00115
				-- 产后留休  00116
				-- 陪产假  00117
				-- 旷工   00118

				--添加没有请假类的人员信息
				INSERT INTO bd_day_user_jial (user_serial,date)
				select user_serial,@yesterday from dt_user where user_type !=51
				and user_serial not in (
					select
						user_serial
					from lr_kqingj
					where  CONVERT(VARCHAR(10),czrq,120) = @yesterday and whether_validity = 0);

				set @result = @@ROWCOUNT;


		--  提交事务
			COMMIT TRAN

	--  结束异常捕捉
		END TRY

	--  异常处理
		BEGIN CATCH
			DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
			IF XACT_STATE() <> 0
			BEGIN
			  ROLLBACK TRANSACTION;
			  RAISERROR (@errStr, 16, 1);
			END;
			ROLLBACK TRAN;
		END CATCH



--  清除临时表
	DROP TABLE #temp;
	select @result as result

-- 打开打印受影响行数
SET NOCOUNT OFF;
END;
